package edu.tms.dao;

import java.util.List;
import java.util.Map;

import edu.tms.bean.Course;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;

/**
 * 课程信息dao
 * @author Administrator
 *
 */
public class CourseDaoImp implements CommnonDao<Course>{
		
	/**
	 * 保存
	 */
	@Override
	public long save(Course value) throws Exception {
		String sql="INSERT INTO t_course(course_name,tch_id,major_name,course_all_periods,course_duration,create_time,course_notice,course_state)";
				sql+="VALUE(?,?,?,?,?,?,?,?)";
		return JdbcUtil.insertAuto(sql, 
				value.getCourseName(),
				value.getTchId(),
				value.getMajorName(),
				value.getCourseAllPeriods(),
				value.getCourseDuration(),
				value.getCreateTime(),
				value.getCourseNotice(),
				value.getCourseState());
	}
	/**
	 * 修改
	 */
	@Override
	public int update(Course value) throws Exception {
			String sql="UPDATE t_course ";
			sql+="SET tch_id=?,major_name=?,course_all_periods=?,course_duration=?,create_time=?,course_notice=?,course_state=?"; 
			sql+="WHERE course_id=?";
			return JdbcUtil.update(sql, 
					value.getTchId(),
					value.getMajorName(),
					value.getCourseAllPeriods(),
					value.getCourseDuration(),
					value.getCreateTime(),
					value.getCourseNotice(),
					value.getCourseState(),
					value.getCourseId());
	}
	/**
	 * 删除
	 */
	@Override
	public int delete(Course value) throws Exception {
		return 0;
	}
	
	//查询所有课程名称和id
	@SuppressWarnings("unchecked")
	public List<Course> findAll() throws Exception{
		String sql="SELECT course_name,course_id FROM t_course;";
		List<Course> cours=JdbcUtil.query(Course.class, sql);
		return cours;
	}
	/**
	 * 分页查询
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Course> findAll(int pageNo, int pageSize) throws Exception {
		String sql="SELECT t_course.course_id,t_course.course_name,t_teacher.tch_name,"
				+ "t_course.major_name,t_course.course_all_periods,t_course.course_duration,"
				+ "t_course.create_time,t_course.course_notice,t_course.course_state "
				+ " FROM t_course,t_teacher WHERE t_course.tch_id=t_teacher.tch_id LIMIT ?,? ";	
		return JdbcUtil.query(Course.class, sql,(pageNo-1)*pageSize,pageSize);
	}
	
	/**
	 * 
	 * 根据选择的班级进行课程
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Course> findCourse(String classinfoName) throws Exception{
		String sql=" SELECT tc.course_name ";
				sql+=" FROM t_course tc,t_class_info tci,t_class_course tcc ";
				sql+=" WHERE tcc.course_id=tc.course_id AND tcc.class_id=tci.class_id AND tci.class_name=? ";
		return JdbcUtil.query(Course.class, sql,classinfoName);
	}
	
	/**
	 * 查询教师所教授的班级中的课程
	 * @param tchId
	 * @param classId
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Course> findAll(Integer tchId, Integer classId) throws Exception {
		String sql="SELECT c.course_name,c.course_id FROM t_course c, t_class_course cc "
				+ "WHERE c.course_id = cc.course_id AND cc.class_id = ? AND c.tch_id = ?;";
		return JdbcUtil.query(Course.class, sql, classId, tchId);
	}
	
	/**
	 * 查询教师所教授的所有课程
	 * @param tchId
	 * @param classId
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Course> findAll(Integer tchId) throws Exception {
		String sql="SELECT course_name,course_id FROM t_course  where tch_id = ?";
		return JdbcUtil.query(Course.class, sql, tchId);
	}
	
	/**
	 * String 对应  数据库 列名（comment)
	 * Object 对应相应列名值
	 * 返回的是所查找的类中的数据。方便EXCEL导出
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	/**
	 * 条件分页查询
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Course> findAll(Object key, int pageNo, int pageSize) throws Exception {
		String sql=" SELECT tc.course_id,tc.course_name,tt.tch_name,tc.major_name,tc.course_all_periods, ";
		sql+=" tc.course_duration,tc.create_time,tc.course_notice,tc.course_state ";
		sql+=" FROM t_course tc,t_teacher tt ";
		sql+=" WHERE tc.tch_id=tt.tch_id AND tc.course_id=? OR tc.course_name=? OR tt.tch_name=? LIMIT ?,?";
		return JdbcUtil.query(Course.class, sql, key,key,key,(pageNo-1)*pageSize,pageSize);
	}
	
	
	@Override
	public Course findByKey(Object key) throws Exception {
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public List<Course> findByTchId(Integer tchId) throws Exception{
		return JdbcUtil.query(Course.class, "select * from t_course where tch_id = ?", tchId);
	}
	
	/**
	 * 根据老师编号查询班级名称
	 * @param tchId
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Course> queryClassName(int tchId) throws Exception {
		String sql="SELECT t_class_info.class_name FROM t_course"
				+ " LEFT JOIN t_class_info ON t_class_info.major_name=t_course.major_name"
				+ " WHERE t_course.tch_id=?";
		return JdbcUtil.query(Course.class, sql, tchId);
	}
	
	//查询专业名称以及课程名称
	public Course queryName(String className,Integer tchId) throws Exception{
		String sql="SELECT c.major_name,c.course_name FROM t_course AS c"
				+ " LEFT JOIN t_teacher AS t ON t.tch_id=c.tch_id"
				+ " LEFT JOIN t_major AS m ON m.major_name=c.major_name"
				+ " LEFT JOIN t_class_info AS tc ON tc.major_name=m.major_name"
				+ " WHERE tc.class_name=? AND t.tch_id=?";
		Course cour=(Course) JdbcUtil.uniqueBean(Course.class, sql, className,tchId);
		return cour;
	}
}
